package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblYyXdfCompanyMySql;

public class TblYyXdfCompanyMySqlMapperSqlConifg {


    public String selectListByPageInfo(PageInfo<TblYyXdfCompanyMySql> pageInfo) {
    	TblYyXdfCompanyMySql condition = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT XC.*,TS.REALNAME FROM TBL_YY_XDF_COMPANY XC LEFT JOIN TBL_STAFF TS ON XC.STAFFID= TS.STAFFID WHERE XC.TEAMID="+condition.getTeamid());
        if(condition.getStaffid()!=null && !"".equals(condition.getStaffid())){
            sqlSb.append(" and XC.STAFFID ="+condition.getStaffid());
        }
        if(condition.getFxtype()!=null && !"".equals(condition.getFxtype())){
            sqlSb.append(" and XC.fxtype ='"+condition.getFxtype()+"' ");
        }
        if (condition.getCompanyname() != null && !"".equals(condition.getCompanyname())) {
            sqlSb.append(" AND XC.COMPANYNAME LIKE '%" + condition.getCompanyname() + "%'");
        }
        sqlSb.append(" ORDER BY XC.COMPANYID) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }
    public String selectCountByPageInfo(PageInfo<TblYyXdfCompanyMySql> pageInfo) {
    	TblYyXdfCompanyMySql condition = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_YY_XDF_COMPANY XC LEFT JOIN TBL_STAFF TS ON XC.STAFFID= TS.STAFFID WHERE XC.TEAMID="+condition.getTeamid());
        if(condition.getStaffid()!=null && !"".equals(condition.getStaffid())){
            sqlSb.append(" and XC.STAFFID ="+condition.getStaffid());
        }
        if(condition.getFxtype()!=null && !"".equals(condition.getFxtype())){
            sqlSb.append(" and XC.FXTYPE ='"+condition.getFxtype()+"' ");
        }
        if (condition.getCompanyname() != null && !"".equals(condition.getCompanyname())) {
            sqlSb.append(" AND XC.COMPANYNAME LIKE '%" + condition.getCompanyname() + "%'");
        }

        return sqlSb.toString();
    }

    public String findBySqlPage(PageInfo<TblYyXdfCompanyMySql> pageInfo) {
    	TblYyXdfCompanyMySql condition = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT XC.*,TS.REALNAME FROM TBL_YY_XDF_COMPANY XC LEFT JOIN TBL_STAFF TS ON XC.STAFFID= TS.STAFFID WHERE XC.ORGID="+condition.getOrgid()+" and XC.STAFFID="+condition.getStaffid());
        if(condition.getFxtype()!=null && !"".equals(condition.getFxtype())){
            sqlSb.append(" and XC.FXTYPE ='"+condition.getFxtype()+"' ");
        }
        if (condition.getCompanyname() != null && !"".equals(condition.getCompanyname())) {
            sqlSb.append(" AND XC.COMPANYNAME LIKE '%" + condition.getCompanyname() + "%'");
        }
        sqlSb.append(" ORDER BY XC.COMPANYID) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findCountBySqlPage(PageInfo<TblYyXdfCompanyMySql> pageInfo) {
    	TblYyXdfCompanyMySql condition = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) FROM TBL_YY_XDF_COMPANY XC LEFT JOIN TBL_STAFF TS ON XC.STAFFID= TS.STAFFID WHERE XC.ORGID="+condition.getOrgid()+" and XC.STAFFID="+condition.getStaffid());

        if(condition.getFxtype()!=null && !"".equals(condition.getFxtype())){
            sqlSb.append(" and XC.FXTYPE ='"+condition.getFxtype()+"' ");
        }
        if (condition.getCompanyname() != null && !"".equals(condition.getCompanyname())) {
            sqlSb.append(" AND XC.COMPANYNAME LIKE '%" + condition.getCompanyname() + "%'");
        }

        return sqlSb.toString();
    }

    public String insertCompany(TblYyXdfCompanyMySql company) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_YY_XDF_COMPANY (COMPANYID");
        StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval");

        if(company.getCompanyname() != null && !"".equals(company.getCompanyname())) {
            column.append(",COMPANYNAME");
            value.append(",'"+company.getCompanyname()+"'");
        }
        if(company.getTeamid() != null && !"".equals(company.getTeamid())) {
            column.append(",TEAMID");
            value.append(",'"+company.getTeamid()+"'");
        }
        if(company.getCreatedate() != null) {
            column.append(",CREATEDATE");
            value.append(",TO_DATE('"+DateUtil.parseDate(company.getCreatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(company.getStaffid() != null) {
            column.append(",STAFFID");
            value.append(",'"+company.getStaffid()+"'");
        }
        if(company.getOrgid() != null ) {
            column.append(",ORGID");
            value.append(",'"+company.getOrgid()+"'");
        }
        if(company.getReportid() != null) {
            column.append(",REPORTID");
            value.append(",'"+company.getReportid()+"'");
        }
        if(company.getFxtype() != null && !"".equals(company.getFxtype())) {
            column.append(",FXTYPE");
            value.append(",'"+company.getFxtype()+"'");
        }
        if(company.getCstatus() != null && !"".equals(company.getCstatus())) {
            column.append(",CSTATUS");
            value.append(",'"+company.getCstatus()+"'");
        }
        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }




}
